package com.wanmait.movies.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.omg.Messaging.SyncScopeHelper;

import com.wanmait.movies.util.DBHelper;
import com.wanmait.movies.vo.Discuss;
import com.wanmait.movies.vo.Movie;
import com.wanmait.movies.vo.MovieLanguage;
import com.wanmait.movies.vo.MovieType;
import com.wanmait.movies.vo.Pager;
import com.wanmait.movies.util.DBHelper;

import java.util.ArrayList;

import com.wanmait.movies.util.DBHelper;
import com.wanmait.movies.vo.Discuss;

import com.wanmait.movies.vo.Movie;
import com.wanmait.movies.vo.MovieLanguage;
import com.wanmait.movies.vo.MovieType;

import com.wanmait.movies.vo.User;

public class DiscussDao {
	private DiscussDao() {}
	private static DiscussDao discussDao = new DiscussDao();
	public static DiscussDao getObj() {
		return discussDao;
	}
	
	
	 public ArrayList<Discuss> DisecussAll(String discussid) {//�ҳ����������йص��������۵�id
		 ArrayList<Discuss>alldiscuss=new ArrayList<Discuss>();
		 Connection co=DBHelper.getDBHelper().getconn();
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 String sql="SELECT id FROM discuss WHERE re_id IN (SELECT id from(SELECT * FROM discuss WHERE id in ("+discussid+")) AS m) ";
		 try {
			ps=co.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Discuss discuss=new Discuss();
				discuss.setId(rs.getInt("id"));
				alldiscuss.add(discuss);
			}
			return alldiscuss;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getDBHelper().close(rs, ps, co);
		 }
		 return null;
	 }
//public static void main(String[] args) {
//	String discussid="1";
//	DiscussDao.getObj().deleteall(discussid);
//	
//}
	 
	//获得总记录数  总行数
		public int getRows()
		{
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			conn = DBHelper.getDBHelper().getconn();
			
			String sql = "select count(*) as c from discuss";
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				
				rs.next();//行指针到第一行
				
				int rows = rs.getInt("c");
				
				return rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBHelper.getDBHelper().close(rs, ps, conn);
			}
			
			return 0;//出现异常
		}
		
		
		
	public void deleteall(String discussid) {//删除该评论和所有有关的评论
		ArrayList<Discuss> alldiscuss=DiscussDao.getObj().DisecussAll(discussid);
		DiscussDao.getObj().DiscussDelete(discussid);
		String str="";
		for(Discuss id:alldiscuss) {
			str+=","+id.getId();
		}
		if(str!="") {
			str=str.substring(1);
			deleteall(str);
		}
	}
	
	/* public void DiscussDelete(String discussid) {//ɾ���ҵ�����
		    Connection con=	DBHelper.getDBHelper().getconn();
		    PreparedStatement ps=null;
		    String sql="delete from discuss where id in("+discussid+")";
		    try {
				ps=con.prepareStatement(sql);
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    finally {
		    	DBHelper.getDBHelper().close(null, ps, con);
		    }
		    }
	 */
	 
	 
	 
	 public int getUnCkeck(int userId) {//���ж���������δ�鿴
		 Connection con=DBHelper.getDBHelper().getconn();
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 int uncheck=0;
		 StringBuilder sb =new StringBuilder();
		 sb.append(" SELECT COUNT(id) as max FROM discuss WHERE re_id IN ");
		 sb.append(" (SELECT id FROM discuss WHERE user_id=?) and check_bit=0 ");
		 String sql=sb.toString();
		 try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, userId);
			rs=ps.executeQuery();
			rs.next();
			uncheck=rs.getInt("max");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getDBHelper().close(rs, ps, con);
		 }
		 return uncheck;
	 }
	 
	 //点击收到消息后，将数据库中的评论我的评论id取出来
	 public ArrayList<Integer> shoudaoxiaoxi(int userId) {
		 Connection con=DBHelper.getDBHelper().getconn();
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 ArrayList<Integer> num=new ArrayList();
		String sql="SELECT id FROM discuss WHERE re_id IN (SELECT id FROM discuss WHERE user_id=?)";
		 try {
			ps=con.prepareStatement(sql); 
			ps.setInt(1, userId);
			rs=ps.executeQuery();
			while(rs.next()) {
				num.add(rs.getInt("id"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getDBHelper().close(rs, ps, con);
		 }
		return num;
		
	 }
	 
	 //改变数据库中评论我的评论的bit值
	 public void getBit(ArrayList<Integer> num) {
		 Connection con=DBHelper.getDBHelper().getconn();
		 PreparedStatement ps=null; 
		 ResultSet rs=null;
		String sql="UPDATE discuss SET check_bit=1 WHERE id IN( ";
		String str="";
		for(int a:num) {
			str+=","+a;
		}
		if(str.equals("")) {
			
		}
		else {
			str=str.substring(1);
		}
		
		sql=sql+str+")";
		 try {
			ps=con.prepareStatement(sql); 
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getDBHelper().close(rs, ps, con);
		 }
		
		
	 }
	 
	 
	//获得要处理举报那条评论的id
	public int getDicussId(int id) {
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql ="select discuss.id from discuss,report where report.id=? and report.discuss_id = discuss.id";
		try {
			
			ps=co.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			rs.next();
			
			int ids = rs.getInt("discuss.id");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, co);
			
		}
		return 0;
	}
	
	//修改要处理举报那条评论的id
	
		public void reWriteDicussId(int id) {
			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			String sql ="update discuss set hidden = 1 where id=?";
			try {
				
				ps=co.prepareStatement(sql);
				ps.setInt(1, id);
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
			DBHelper.getDBHelper().close(rs, ps, co);
			}
		}


	public void check(int userid) {//�鿴����Ϣ�����ݿ��е�bitֵ��Ϊ1
	      
	     StringBuilder sb=new StringBuilder();
	     sb.append(" UPDATE discuss SET check_bit=1 WHERE re_id IN ");
	     sb.append(" ( SELECT id from ");
	     sb.append(" (SELECT id FROM discuss WHERE user_id=?) AS  a )   ");
	     String sql=sb.toString();
	     DBHelper.getDBHelper().update(sql, userid);
	     
	   }
	   

	public ArrayList<Discuss> alldiscuss(int userId,int thispage){//���������۵ĵ�Ӱ
		ArrayList<Discuss>alldiscuss=new ArrayList<Discuss>();
		Connection con=DBHelper.getDBHelper().getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select * FROM ");
		sb.append(" discuss,`user`,movie,movie_type,movie_language ");
		sb.append(" WHERE  ");
		sb.append(" user_id=? ");
		sb.append(" AND ");
		sb.append(" user_id=user.id ");
		sb.append(" AND ");
		sb.append(" movie_id=movie.id ");
		sb.append(" AND ");
		sb.append(" movie_type_id=movie_type.id ");
		sb.append(" AND ");
		sb.append(" movie_language_id=movie_language.id ");
		sb.append(" order by discuss_time DESC ");
		sb.append(" LIMIT ?,5 ");
		String sql=sb.toString();
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, (thispage-1)*5);
			rs=ps.executeQuery();
			while(rs.next()) {
				Discuss discuss=new Discuss();
				discuss.setId(rs.getInt("discuss.id"));
				discuss.setCheckBit(rs.getBoolean("check_bit"));
				discuss.setDiscussTime(rs.getTimestamp("discuss_time"));
				discuss.setMovieDiscuss(rs.getString("movie_discuss"));
				Movie movie=new Movie();
				movie.setId(rs.getInt("movie.id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));
				movie.setMovieIntroduction(rs.getString("movie_introduction"));
				movie.setMovieShowtime(rs.getTimestamp("movie_showtime"));
				MovieType type=new MovieType();
				type.setId(rs.getInt("movie_type.id"));
				type.setMovieType(rs.getString("movie_type"));
				//System.out.println(type.getMovieType());
				MovieLanguage language=new MovieLanguage();
				language.setId(rs.getInt("movie_language.id"));
				language.setLanguageType(rs.getString("language_type"));
				movie.setMovieLanguageId(language);
				movie.setMovieTypeId(type);
				User user=new User();
				user.setId(rs.getInt("user.id"));
				user.setUserName(rs.getString("user_name"));
				user.setUserFaceimage(rs.getString("user_faceimage"));
				discuss.setMovieId(movie);
				discuss.setUserId(user);
				alldiscuss.add(discuss);
			}
			return alldiscuss;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, con);
		}
		return null;
	}
	public int getpagemax(int userId) {//���ܹ������˼�����Ӱ
		Connection con=DBHelper.getDBHelper().getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		int max=0;
		String sql="select count(id) as m from discuss where user_id=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, userId);
			rs=ps.executeQuery();
			rs.next();
			max=rs.getInt("m");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, con);
		}
		return max;
	}
	public ArrayList<Discuss>rediscuss(int userId,int thispage){//�����ҵ�id �ҳ����������ҵ�����
		 ArrayList<Discuss>alldiscuss=new ArrayList<Discuss>();
		 Connection con=DBHelper.getDBHelper().getconn();
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 StringBuilder sb=new StringBuilder();
		 sb.append(" WITH m as ");//�Ȳ����������е����ۣ���Ϊһ����
		 sb.append(" (SELECT * FROM ");
		 sb.append(" discuss ");
		 sb.append(" where ");
		 sb.append(" user_id=? ");
		 sb.append(" ) ");
		 sb.append(" SELECT * from ");//�ٽ���������������һ���½��ı� ��ȷid�����������id�Ͳ�ͬ�ˣ����趨����
		 sb.append(" discuss,m,`user`,movie ");
		 sb.append(" where ");
		 sb.append(" m.id=discuss.re_id ");
		 sb.append(" and ");
		 sb.append(" movie.id=discuss.movie_id ");
		 sb.append(" and ");
		 sb.append(" user.id=discuss.user_id ");
		 sb.append(" ORDER BY discuss.discuss_time DESC ");
		 sb.append(" LIMIT ?,5 ");
		 String sql=sb.toString();
		 try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, (thispage-1)*5);
			rs=ps.executeQuery();
			while(rs.next()) {
				Discuss discuss=new Discuss();
				discuss.setId(rs.getInt("discuss.id"));//�����ҵ����ݵ�id
				discuss.setDiscussTime(rs.getTimestamp("discuss.discuss_time"));//���˵�����ʱ��
				discuss.setMovieDiscuss(rs.getString("m.movie_discuss"));//�ҵ�����
				Movie movie=new Movie();
				movie.setId(rs.getInt("movie_id"));//����һ����Ӱ�µ�����
				movie.setMovieName(rs.getString("movie_name"));
				movie.setMovieImage(rs.getString("movie_image"));//�ǲ���Ӱ������ͼ ���ֱ�ӽ����Ӱҳ��
				discuss.setMovieId(movie);
				discuss.setReDiscuss(rs.getString("discuss.movie_discuss"));//������
				User user=new User();
				user.setId(rs.getInt("user_id"));
				user.setUserFaceimage(rs.getString("user_faceimage"));
				user.setUserName(rs.getString("user_name"));
				discuss.setUserId(user);//�����ҵ��Ǹ��˵�ͷ�������
				alldiscuss.add(discuss);
			}
			return alldiscuss;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getDBHelper().close(rs, ps, con);
		 }
		 return null;
	 }
	 public int repagemax(int userId) {//���ܹ��ж��������ظ�
		 Connection con=DBHelper.getDBHelper().getconn();
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 int max=0;
		 StringBuilder sb=new StringBuilder();
		 sb.append(" WITH m as ");
		 sb.append(" (SELECT * FROM ");
		 sb.append(" discuss ");
		 sb.append(" where ");
		 sb.append("  user_id=? ");
		 sb.append("  ) ");
		 sb.append("  , n AS ");
		 sb.append("  (SELECT m.id from ");
		 sb.append("  discuss,m ");
		 sb.append("  where ");
		 sb.append(" m.id=discuss.re_id) ");
		 sb.append(" SELECT COUNT(n.id) as max ");
		 sb.append(" FROM n ");
		 String sql=sb.toString();
		 try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, userId);
			rs=ps.executeQuery();
			rs.next();
			max=rs.getInt("max");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getDBHelper().close(rs, ps, con);
		 }
		 return max;
	 }
	
	 
	 
	 
	 public void DiscussDelete(String discussid) {//删除该评论
		    Connection con=	DBHelper.getDBHelper().getconn();
		    PreparedStatement ps=null;
		    String sql="delete from discuss where id in("+discussid+")";
		    try {
				ps=con.prepareStatement(sql);
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    finally {
		    	DBHelper.getDBHelper().close(null, ps, con);
		    }
	  }

	//���õ�Ӱ�µ��������۴����ݿ�ȡ����
	public ArrayList<Discuss> getallDiscuss(int movieId,Pager pager) {
		ArrayList<Discuss> allDiscuss=new ArrayList<>();
		Connection conn = DBHelper.getDBHelper().getconn();
		String sql="WITH x AS (SELECT discuss.*,`user`.user_name,`user`.user_faceimage FROM discuss,`user` WHERE discuss.user_id=`user`.id),y AS(SELECT discuss.*,discuss.id AS dis_id,`user`.user_name,`user`.user_faceimage FROM discuss,`user` WHERE discuss.user_id=`user`.id) SELECT * FROM x LEFT JOIN y ON x.re_id=y.dis_id WHERE x.movie_id=? ORDER BY x.discuss_time asc  limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,movieId);
			ps.setInt(2, (pager.getCurrentPager()-1)*10);
			ps.setInt(3, pager.getCountinPager());
			rs=ps.executeQuery();
			while(rs.next()) {
				Discuss j=new Discuss();
				j.setMovieDiscuss(rs.getString("movie_discuss"));
				j.setDiscussTime(rs.getTimestamp("discuss_time"));
				j.setId(rs.getInt("x.id"));
				User user=new User();
				user.setUserName(rs.getString("user_name"));
				user.setId(rs.getInt("user_id"));
				user.setUserFaceimage(rs.getString("user_faceimage"));
				j.setUserId(user);
				j.setReId(rs.getInt("re_id"));
				User reUser=new User();
				reUser.setId(rs.getInt("dis_id"));
				reUser.setUserFaceimage(rs.getString("y.user_faceimage"));
				reUser.setUserName(rs.getString("y.user_name"));
				j.setReUser(reUser);
				j.setReMovieDiscuss(rs.getString("y.movie_discuss"));
				
				j.setRehidden(rs.getBoolean("y.hidden"));
				j.setHidden(rs.getBoolean("x.hidden"));
				
				allDiscuss.add(j);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return allDiscuss;
		
	}
	
	
	//���û��Ըõ�Ӱ��������ӵ����ݿ�
	public void pinglun(int userId,String discuss,int movieId) {
		Connection conn = DBHelper.getDBHelper().getconn();
		String sql="insert into discuss(movie_id,movie_discuss,user_id) values(?,?,?)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,movieId);
			ps.setString(2, discuss);
			ps.setInt(3, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
	}
	//���û��������û���������ӵ����ݿ�
	public void pingluntaren(int userId,String discuss,int reId,int movieId) {
		Connection conn = DBHelper.getDBHelper().getconn();
		String sql="insert into discuss(movie_discuss,user_id,re_id,movie_id) values(?,?,?,?)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, discuss);
			ps.setInt(2, userId);
			ps.setInt(3, reId);
			ps.setInt(4, movieId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
	}
	//删除评论
	
			public void deleteDiscuss(int id) {
				Connection co = DBHelper.getDBHelper().getconn();
				PreparedStatement ps = null;
				ResultSet rs = null;
				
				String sql ="delete from discuss  where movie_id=?";
				try {
					
					ps=co.prepareStatement(sql);
					ps.setInt(1, id);
					ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
				DBHelper.getDBHelper().close(rs, ps, co);
				}
			}
			//获得要删除的id
			
			public ArrayList<Integer> getDiscussId(int id) {
				Connection co = DBHelper.getDBHelper().getconn();
				PreparedStatement ps = null;
				ResultSet rs = null;
				ArrayList<Integer> list = new ArrayList<Integer>();
				String sql ="select * from discuss  where movie_id=?";
				try {
					
					ps=co.prepareStatement(sql);
					ps.setInt(1, id);
					rs= ps.executeQuery();
					while(rs.next()) {
						list.add(rs.getInt("id"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
				DBHelper.getDBHelper().close(rs, ps, co);
				}
				return list;
			}
}
